# #!/usr/bin/python
# # -*- coding: UTF-8 -*-
import pymysql
import urllib
from urllib.request import urlopen
import requests
import json
import pprint
import os
#读取数据库地址
def list_col(matrix,time):
    db = pymysql.connect("127.0.0.1","root","","yf", charset="utf8")
    cursor = db.cursor()
    sql = "SELECT freightId,bourn FROM all_data WHERE createdate = '%s' ORDER  BY freightId DESC" %time
    cursor.execute(sql)
    myresult = cursor.fetchall()
    db.close()
    for i in range(len(myresult)):
        point = "map"+str(i)
        point = {}
        point["freightId"] = myresult[i][0]
        point["bourn"] = myresult[i][1]
        matrix.insert(i,point)
    return matrix
    #将地址转经纬度
def getlnglat(reslut):
    url = 'http://api.map.baidu.com/geocoder/v2/?address=%s&output=json&ak=otwl42kAYOlhEQUMfnDfFyP6gXUXD6IA'%reslut
    res = requests.get(url)
    print(url)
    json_data = json.loads(res.text)
    if json_data["status"] == 0 :
        lat_lng = {}
        lat_lng["bourn"] = reslut
        lat_lng["lat"] = json_data['result']['location']['lat']
        lat_lng["lng"] = json_data['result']['location']['lng']
        return lat_lng
    #将坐标写入
def insert_db(loc_json):
    db = pymysql.connect("172.17.130.165","root","","test", charset="utf8")
    cursor = db.cursor()
    # print(loc_json)
    add = loc_json["bourn"]
    lng = loc_json["lng"]
    lat = loc_json["lat"]
    # print(add)
    sql = "update yf_path set longitude = %f,latitude = %f where address = '%s'" % (lng,lat,add)
    cursor.execute(sql)
    db.close()
def distance_all(url,all_data,info):
    b = urlopen(url)
    print(url)
    c=b.read().decode()
    b.close()
    result_dis = json.loads(c)
    if result_dis is not None :
      int_dis = result_dis["result"]["routes"][0]['distance']
      all_data["path"] = {}
      all_data["path"]["coordinate"] = [str(info["lng"]),str(info["lat"])]
      all_data["path"]["addressName"] = info["bourn"]
      all_data["path"]["dsitanceToCenter"] = int_dis
      all_data["distance"] = int_dis
      return all_data
#总部地址
headquarters_lat = "39.957978"
headquarters_lng = "116.806899"
matrix = []
repeated = []
completed = []
#reslut为查询的所有地址
time = "2016-04-03"
matrix = list_col(matrix,time)

for i in range(len(matrix)):
    j = i+1
    if matrix[i]["freightId"] not in repeated:
        repeated.append(matrix[i]["freightId"])
        completed_json = "com"+str(i)
        completed_json = {}
        completed_json = matrix[i]
        completed.insert(i,completed_json)
    if j<len(matrix):
        if matrix[j]["freightId"] in repeated and matrix[j]["bourn"] != matrix[i]["bourn"]:
            completed_json = "com"+str(i)
            completed_json = {}
            completed_json = matrix[j]
            completed.insert(j,completed_json)
print(completed[1])
finnal = []
for i in range(len(completed)):
    lng_lat = "lng_lat"+str(i)
    lng_lat = {}
    print(completed[i]["bourn"])
    if getlnglat(completed[i]["bourn"]) is not None:
        lng_lat = getlnglat(completed[i]["bourn"])
        lng_lat["freightId"] = completed[i]["freightId"]
        finnal.insert(i,lng_lat)
#处理多余数据
dis = 0
print(finnal)
all_path={}
data_insert = {}
data_insert["Daydistance"] = 0
data_insert["pathinfo"] = []
for i in range(len(finnal)):
    first_lat = headquarters_lat
    first_lng = headquarters_lng
    second_lat = str(finnal[i]["lat"])
    second_lng = str(finnal[i]["lng"])
    url='http://api.map.baidu.com/directionlite/v1/driving?origin='+first_lat+','+first_lng+'&destination='+second_lat+','+second_lng+'&ak=' 
    ak = 'a0GX207kH3c0t0Xlr40dLGUQMiNda7s4'
    output = 'json'
    url2 = url+ak+'&output='+output
    path = "path"+str(i)
    path = {}
    ret_dis = distance_all(url2,path,finnal[i])
    data_insert["pathinfo"].insert(i,ret_dis)
    data_insert["Daydistance"] += 2*int(ret_dis["distance"])
all_path["code"] = 0
all_path["msg"] = "Success"
all_path["data"] = []
all_path["data"].append(data_insert)
# print(all_path)
path = os.getcwd()
file_name ='%s\public\static\json\old-%s.json' % (path,time.replace('-', ''))
insert_time ='%s\public\static\json\insert_time.txt' % path
fp = open(file_name,'w+',encoding="utf-8")
time_add = open(insert_time,'a+',encoding="utf-8")
time_repead = open(insert_time)
print(time_repead.read().split(','))
print(len(time_repead.read().split(',')))
for i in range(len(time_repead.read().split(','))):
    if(i==len(time_repead.read().split(','))-1):
        time_add.write(time+',')
    if(time!=time_repead.read().split(',')[i]):
        continue
    if(time==time_repead.read().split(',')[i]):
        break
fp.write(json.dumps(all_path,sort_keys=True, indent=4, separators=(', ', ': '),ensure_ascii=False))
# print("20160101共行驶了%s公里" % (dis/1000))
# print("共出了%s趟车" % (len(completed)))


        
